using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;

namespace MvcExtExample.ReportViews
{
    public partial class OilFieldMonthReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["LogedUser"] == null)
                Response.Redirect("http://localhost:8132/home/index");
            if (!IsPostBack)
            {
                //获取所有工作区的名称
                this.WorkZoneDropDownList.DataSource = GetWorkZoneData();
                this.WorkZoneDropDownList.DataBind();
                //获取“第一作业区”下的生产大队列表
                this.SCDDDropDownList.DataSource = GetSCDDData("第一作业区");
                this.SCDDDropDownList.DataBind();
                this.productdate_box.Value = DateTime.Now.ToString("yyyy-MM");
                ReportViewer1.LocalReport.Refresh();
                DataTable dt = GetData();
                ReportDataSource rds = new ReportDataSource("OilFieldMonthReportDataSet", dt);
                this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("..\\Reports\\OilFieldMonthReport.rdlc");
                ReportViewer1.KeepSessionAlive = true;//解决ASP.NET session has expired or could not be found问题
                ReportViewer1.LocalReport.DataSources.Clear();
                ReportViewer1.LocalReport.DataSources.Add(rds);
                ReportViewer1.LocalReport.Refresh();
            }
        }

        //从数据库读取报表所需数据
        private DataTable GetData()
        {
            List<string> Date = new List<string>();
            List<decimal> CYJDailyOil = new List<decimal>(), CYJDailyWater = new List<decimal>(), CYJDailyFluid = new List<decimal>(), CYJDailyContainWater = new List<decimal>(), ZSJDaily_Schedule = new List<decimal>(), ZSJDaily_Actual = new List<decimal>(), CQJDailyGas = new List<decimal>(), CQJDailyWater = new List<decimal>(), CQJDailyOil = new List<decimal>();
            List<int> TotalWells = new List<int>(),CYJOpenWells = new List<int>(),ZSJOpenWells = new List<int>(),CQJOpenWells = new List<int>();
            string productdate = this.productdate_box.Value;
            string workZone = this.WorkZoneDropDownList.SelectedValue;
            string SCDD = this.SCDDDropDownList.SelectedValue;
            string strSqlStr = "SELECT  * FROM [dbo].[Tb_Oil_Well]";
            //获取这一个月内每天的井总数
            #region
            //获取这一个月的每天日期
            string month = productdate.Substring(productdate.Length-1,1);
            string year = productdate.Substring(0,4);
            string strSqlGetDateList = "SELECT DISTINCT [Date] FROM [dbo].[Tb_Oil_Well] WHERE MONTH([Date])="+month +" AND YEAR([Date])="+year;
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            DataSet DateDateSet = cs.GetDataSet(strSqlGetDateList);
            if (DateDateSet != null)
            {
                DataTable DTable = DateDateSet.Tables[0];
                for (int i = 0; i < DTable.Rows.Count; i++)
                {
                    DataRowView rowview = DateDateSet.Tables[0].DefaultView[i];
                    Date.Add(rowview["Date"].ToString());
                }
            }
            //获取这一个月内每天的井数
            foreach(string item in Date)
            {
                string strSqlGetDailyOilWell = "SELECT COUNT(*)FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "'";
                string strSqlGetDailyWaterWell = "SELECT COUNT(*)FROM [dbo].[Tb_Water_Injection_Well]WHERE [Date] = '" + item + "'";
                string strSqlGetDailyTb_Gas_Well = "SELECT COUNT(*)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "'";
                int DailyOilWellNum = cs.ReturnSQL(strSqlGetDailyOilWell);
                int DailyWaterWellNum = cs.ReturnSQL(strSqlGetDailyWaterWell);
                int DailyGasWellNum = cs.ReturnSQL(strSqlGetDailyTb_Gas_Well);
                CYJOpenWells.Add(DailyOilWellNum);
                ZSJOpenWells.Add(DailyWaterWellNum);
                CQJOpenWells.Add(DailyGasWellNum);
                TotalWells.Add(DailyOilWellNum + DailyWaterWellNum + DailyGasWellNum);
            }
            //获取这一个月内每天的油井相关指标
            foreach (string item in Date)
            {
                //获取这一个月内每天的日产油总量
                string strSqlGetDailyOil = "SELECT SUM([DailyOil])FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "'";
                decimal DailyTotalOil = cs.ReturnDecimalSQL(strSqlGetDailyOil);
                CYJDailyOil.Add(DailyTotalOil);
                //获取这一个月内每天的日产水总量
                string strSqlGetDailyWater = "SELECT SUM(DailyFluid)FROM [dbo].[Tb_Oil_Well]WHERE [Date] = '" + item + "'";
                decimal DailyTotalFluid = cs.ReturnDecimalSQL(strSqlGetDailyWater);
                CYJDailyWater.Add(DailyTotalFluid);
            }
            //获取这一个月内每天的水井相关指标
            foreach (string item in Date)
            {
                //获取这一个月内每天的标注
                string strSqlGetDaily_Schedule = "SELECT SUM(Daily_Schedule)FROM [dbo].Tb_Water_Injection_Well WHERE [Date] = '" + item + "'";
                decimal Daily_Schedule = cs.ReturnDecimalSQL(strSqlGetDaily_Schedule);
                ZSJDaily_Schedule.Add(Daily_Schedule);
                //获取这一个月内每天的实注
                string strSqlGetDaily_Actual = "SELECT SUM(Daily_Actual)FROM [dbo].[Tb_Water_Injection_Well]WHERE [Date] = '" + item + "'";
                decimal Daily_Actual = cs.ReturnDecimalSQL(strSqlGetDaily_Actual);
                ZSJDaily_Actual.Add(Daily_Actual);
            }
            //获取这一个月内每天的气井相关指标
            foreach (string item in Date)
            {
                //获取这一个月内每天的日产气
                string strSqlGetDaily_Gas = "SELECT SUM(DailyGas)FROM [dbo].Tb_Gas_Well WHERE [Date] = '" + item + "'";
                decimal Daily_Gas = cs.ReturnDecimalSQL(strSqlGetDaily_Gas);
                CQJDailyGas.Add(Daily_Gas);
                //获取这一个月内每天的日产油
                string strSqlGetDailyOil = "SELECT SUM(DailyOil)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "'";
                decimal DailyOil = cs.ReturnDecimalSQL(strSqlGetDailyOil);
                CQJDailyOil.Add(DailyOil);
                //获取这一个月内每天的日产水
                string strSqlGetDailyWaterCQJ = "SELECT SUM(DailyWater)FROM [dbo].[Tb_Gas_Well]WHERE [Date] = '" + item + "'";
                decimal DailyWaterCQJ = cs.ReturnDecimalSQL(strSqlGetDailyWaterCQJ);
                CQJDailyWater.Add(DailyWaterCQJ);
            }
            #endregion
            DataTable dt = new DataTable("TB_OilFieldReportMonth");//这个名字是无关紧要的，xsd的DataTable也可以随意命名
            dt.Columns.Add(new DataColumn("Date", typeof(string)));
            dt.Columns.Add(new DataColumn("TotalWells", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJOpenWells", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJDailyOil", typeof(string)));
            dt.Columns.Add(new DataColumn("CYJDailyWater", typeof(string)));
            dt.Columns.Add(new DataColumn("ZSJOpenWells", typeof(string)));
            dt.Columns.Add(new DataColumn("ZSJDaily_Schedule", typeof(string)));
            dt.Columns.Add(new DataColumn("ZSJDaily_Actual", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJOpenWells", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJDailyGas", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJDailyWater", typeof(string)));
            dt.Columns.Add(new DataColumn("CQJDailyOil", typeof(string)));
            for (int i = 0; Date.Count > 0 && i < Date.Count; i++)
            {
                DataRow row = dt.NewRow();
                row["Date"] = Convert.ToDateTime(Date[i]).ToShortDateString();
                row["TotalWells"] = TotalWells[i];
                row["CYJOpenWells"] = CYJOpenWells[i];
                row["CYJDailyOil"] = CYJDailyOil[i];
                row["CYJDailyWater"] = CYJDailyWater[i];
                row["ZSJOpenWells"] = ZSJOpenWells[i];
                row["ZSJDaily_Schedule"] = ZSJDaily_Schedule[i];
                row["ZSJDaily_Actual"] = ZSJDaily_Actual[i];
                row["CQJOpenWells"] = CQJOpenWells[i];
                row["CQJDailyGas"] = CQJDailyGas[i];
                row["CQJDailyWater"] = CQJDailyWater[i];
                row["CQJDailyOil"] = CQJDailyOil[i];
                dt.Rows.Add(row);
            }
            return dt;
        }

        //根据工作区名称得到生产大队列表
        private List<string> GetSCDDData(string strSCDD)
        {
            List<string> SCDDList = new List<string>();
            string strSqlStr = "SELECT [Dep_Name] FROM [dbo].[Tb_Department] WHERE [ParentDep_ID] IN (SELECT [Dep_ID] FROM [dbo].[Tb_Department] WHERE [Dep_Name] = '" + strSCDD + "') AND SUBSTRING([dbo].[Tb_Department].[Dep_ID],0,3)='CY'";
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            DataSet DSet = cs.GetDataSet(strSqlStr);
            if (DSet != null)
            {
                DataTable DTable = DSet.Tables[0];
                for (int i = 0; i < DTable.Rows.Count; i++)
                {
                    DataRowView rowview = DSet.Tables[0].DefaultView[i];
                    SCDDList.Add(rowview["Dep_Name"].ToString());
                }
            }
            return SCDDList;
        }

        //获取所有工作区的名称
        private List<string> GetWorkZoneData()
        {
            List<string> WorkZone = new List<string>();
            string strSqlStr = "SELECT [Dep_Name] FROM [dbo].[Tb_Department] WHERE [ParentDep_ID]='ALL'";
            framework.SqlHelperCS.SqlHelper cs = new framework.SqlHelperCS.SqlHelper();
            DataSet DSet = cs.GetDataSet(strSqlStr);
            if (DSet != null)
            {
                DataTable DTable = DSet.Tables[0];
                for (int i = 0; i < DTable.Rows.Count; i++)
                {
                    DataRowView rowview = DSet.Tables[0].DefaultView[i];
                    WorkZone.Add(rowview["Dep_Name"].ToString());
                }
            }
            return WorkZone;
        }

        protected void WorkZoneDropDownList_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.SCDDDropDownList.DataSource = GetSCDDData(WorkZoneDropDownList.SelectedItem.Text);
            this.SCDDDropDownList.DataBind();
        }

        protected void queryBtn_Click(object sender, EventArgs e)
        {
            DataTable dt = GetData();
            ReportDataSource rds = new ReportDataSource("OilFieldMonthReportDataSet", dt);
            this.ReportViewer1.LocalReport.ReportPath = Server.MapPath("..\\Reports\\OilFieldMonthReport.rdlc");
            ReportViewer1.LocalReport.DataSources.Clear();
            ReportViewer1.LocalReport.DataSources.Add(rds);
            ReportViewer1.LocalReport.Refresh();
        }

    }
}